package DataConnectionInterface;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import Other.*;
import DatabaseManager.CharacterInUniverseTable;
import DatabaseManager.CharacterTable;
import DatabaseManager.DBManager;
import DatabaseManager.UniverseTable;
import DatabaseManager.UserTable;
import FreebaseDumps.FileDumper;
import Helper.Misc;
import Helper.GameException;
import Helper.RandomGenerator;

public class GameDataConnection implements GameDataConnectionInterface {
	/**
	 * Constructs a new connection to the database
	 * 
	 * @throws GameException
	 */
	public GameDataConnection() throws GameException {
		try {
			// creating table classes
			db_mgr = new DBManager();
			character_table = new CharacterTable(db_mgr);
			universe_table = new UniverseTable(db_mgr);
			user_table = new UserTable(db_mgr);
			character_in_universe_table = new CharacterInUniverseTable(db_mgr);

		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		} catch (ClassNotFoundException e) {
			throw new GameException(5, e.getMessage());
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#CreateDataBase(java
	 * .lang.String, java.lang.String)
	 */
	public void CreateDataBase(String character_tsv, String universe_tsv)
			throws GameException {
		try {
		// rebuilding database & dumping data
		BuildDataBase(character_tsv, universe_tsv);
		// creating connection between universe and cards
		CreateCharacterInUniverseTable();

			// change engines to InnoDB
			character_table.toInnoDB();
			universe_table.toInnoDB();
			character_in_universe_table.toInnoDB();
			user_table.toInnoDB();

			// add foreign key constraints
			character_in_universe_table.addForeignKeyConstraint("FK1",
					"character_id", character_table.GetTableName() + "("
							+ character_table.GetPrimaryKey() + ")");
			character_in_universe_table.addForeignKeyConstraint("FK2",
					"universe_id", universe_table.GetTableName() + "("
							+ universe_table.GetPrimaryKey() + ")");
			
			// create indices
			character_table.CreateIndex("name", "BTREE", "INDEX1");
			// universe name has a unique index
			character_in_universe_table.CreateIndex("universe_id", "BTREE",
					"INDEX2");
			character_in_universe_table.CreateIndex("character_id", "HASH",
					"INDEX3");
			user_table.CreateIndex("username", "BTREE", "INDEX4");
			user_table.CreateIndex("wins", "BTREE", "INDEX5");
			
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#getCardsFromUniverse
	 * (java.lang.String, int)
	 */
	public List<Card> getCardsFromUniverse(String universeName,
			int numberOfCards) throws GameException {
		String query;
		universeName = Misc.correctString(universeName);
		String ctn = character_table.GetTableName(); // character's table name
		String ciutn = character_in_universe_table.GetTableName();

		String utn = universe_table.GetTableName();
		query = "SELECT DISTINCT " + ctn + ".name, hit_points, strength ";
		query += "FROM " + ctn + ", " + ciutn + ", " + utn;
		query += " WHERE " + ctn + ".character_id = " + ciutn
				+ ".character_id AND " + ciutn + ".universe_id = " + utn
				+ ".universe_id AND " + utn + ".name = '" + universeName
				+ "' LIMIT " + numberOfCards;

		ResultSet rs = null;
		List<Card> cards = null;
		try {
			// get cards from the given universe
			rs = db_mgr.QueryTable(query);
			int count = 0;
			cards = new ArrayList<Card>();

			// put them in a list
			while (count < numberOfCards && rs.next()) {
				count++;
				String characterName = rs.getString(1);
				int hitPoints = rs.getInt(2);
				int strength = rs.getInt(3);
				cards.add(new Card(strength, hitPoints, characterName));
			}

			// complete to a full deck, if not enough cards were inserted
			if (cards.size() < numberOfCards) {
				RandomGenerator randomGenerator = new RandomGenerator();
				int cardsSize = cards.size();
				for (int i = 0; i < numberOfCards - cardsSize; i++)
					cards.add(new Card(cards.get(randomGenerator.getRandomInt(
							0, cardsSize - 1))));
			}

			db_mgr.closeLastStatement();
			// closing mechanism. note that closing the statement
			// also closes the result set (automatically).
		} catch (SQLException e) {
			try {
				db_mgr.closeLastStatement();
				throw new GameException(1, e.getMessage());
			} catch (SQLException e1) {
				throw new GameException(1, e.getMessage());
			}

		}
		
		return cards;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#getLargeUniverses
	 * (int)
	 */
	public List<UniverseInformation> getLargeUniverses(int num)
			throws GameException {
		String universeTableName = universe_table.GetTableName();
		String characterInUniverseTableName = character_in_universe_table
				.GetTableName();
		String query = "SELECT DISTINCT " + characterInUniverseTableName
				+ ".universe_id, name ";
		query += "FROM " + universeTableName + ", "
				+ characterInUniverseTableName;
		query += " WHERE " + universeTableName + ".universe_id = "
				+ characterInUniverseTableName + ".universe_id " + "GROUP BY "
				+ characterInUniverseTableName
				+ ".universe_id, name HAVING count(*) >= " + num;

		ResultSet rs = null;
		List<UniverseInformation> universes = null;
		try {
			// get information about all universes with at least num characters
			rs = db_mgr.QueryTable(query);
			universes = new LinkedList<UniverseInformation>();
			// add them to the list
			while (rs.next()) {
				universes.add(new UniverseInformation(rs.getString(2), rs
						.getInt(1)));
			}

			db_mgr.closeLastStatement();
			// closing mechanism... again, we throw message of first exception
		} catch (SQLException e) {
			try {
				db_mgr.closeLastStatement();
			} catch (SQLException e1) {
				throw new GameException(1, e.getMessage());
			}
			throw new GameException(1, e.getMessage());
		}

		return universes;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#getHighScores(int)
	 */
	public List<UserInfo> getHighScores(int maxNumberOfHighScores)
			throws GameException {
		try {
			return user_table.getHighScores(maxNumberOfHighScores);
			// if there was no exception, already closed
		} catch (SQLException e) {
			try {
				db_mgr.closeLastStatement();
			} catch (SQLException e1) { // again, we give message of first exception
				throw new GameException(1, e.getMessage());
			}
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#updateUserStatistics
	 * (int, boolean)
	 */
	public void updateUserStatistics(int user_id, boolean hasWon)
			throws GameException {
		try {
			user_table.updateUserStatistics(user_id, hasWon);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#authenticateUser(
	 * DataConnectionInterface.UserInfo)
	 */
	public boolean authenticateUser(UserInfo ui) throws GameException {
		try {
			return user_table.authenticate(ui);
		} catch (SQLException e) {
			// if an exception is thrown, everything is closed already
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#updateCharacter()
	 */
	public void updateCharacter(ServerCard card, int characterID)
			throws GameException {
		try {
			String[] values = { "'" + Misc.correctString(card.getName()) + "'",
					String.valueOf(card.getDefense()),
					String.valueOf(card.getAttack()) };
			character_table.UpdateAll(values, "character_id = " + characterID);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#deleteCharacter()
	 */
	public void deleteCharacter(int characterID) throws GameException {
		try {
			character_table.DeleteFrom(character_table.GetPrimaryKey() + " = "
					+ characterID);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#addUniverse(java.
	 * lang.String)
	 */
	public void addUniverse(String universe_name) throws GameException {
		try {
			universe_table.SimpleInsertTable(new String[] {
					Misc.correctString(universe_name)});
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#deleteUniverse(int)
	 */
	public void deleteUniverse(int universe_id) throws GameException {
		try {
			universe_table.DeleteFrom("universe_id = " + universe_id);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seeDataConnectionInterface.GameDataConnectionInterface#
	 * removeCharacterFromUniverse(int, int)
	 */
	public void removeCharacterFromUniverse(int character_id, int universe_id)
			throws GameException {
		try {
			character_in_universe_table.DeleteFrom("character_id = "
					+ character_id + " AND universe_id = " + universe_id);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#insertCharacterToUniverse
	 * (int, int)
	 */
	public void insertCharacterToUniverse(ServerCard card, int universe_id)
			throws GameException {
		try {
			// this is the only statement that involves several inserts
			db_mgr.setAutoCommit(false);
			int character_id = character_table.insertCardAndGetID(card);
			character_in_universe_table
					.SimpleInsertTable(new String[] {
							String.valueOf(character_id),
							String.valueOf(universe_id) });
			db_mgr.commit();
			db_mgr.setAutoCommit(true);
		} catch (SQLException e) {
			try {
			db_mgr.rollback();
			db_mgr.setAutoCommit(true);
			} catch (SQLException e1) {
				try { // in case error was in rollback
					db_mgr.setAutoCommit(true);
					throw new GameException(1, e.getMessage());
				} catch (SQLException e2) {
					throw new GameException(1, e.getMessage());
				} 
			}
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see DataConnectionInterface.GameDataConnectionInterface#deleteUser(int)
	 */
	public void deleteUser(int userID) throws GameException {
		try {
			user_table.DeleteFrom(user_table.GetPrimaryKey() + " = " + userID);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see DataConnectionInterface.GameDataConnectionInterface#insertUser()
	 */
	public void insertUser(User user) throws GameException {
		try {
			user_table.SimpleInsertTable(new String[] {
					Misc.correctString(user.getName()),
					Misc.correctString(user.getPassword()),
					String.valueOf(user.getNumOfWin()),
					String.valueOf(user.getNumOfLose()), Misc.getTodaysDate(),
					Misc.correctString(user.getDescription()) });
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see DataConnectionInterface.GameDataConnectionInterface#updateUser()
	 */
	public void updateUser(User user, int userID) throws GameException {
		try {
			String[] values = { "'" + Misc.correctString(user.getName()) + "'",
					"'" + Misc.correctString(user.getPassword()) + "'",
					String.valueOf(user.getNumOfWin()),
					String.valueOf(user.getNumOfLose()),
					"'" + Misc.getTodaysDate() + "'",
					"'" + Misc.correctString(user.getDescription()) + "'" };
			user_table.UpdateAll(values, "user_id = " + userID);
		} catch (SQLException e) {
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * DataConnectionInterface.GameDataConnectionInterface#getUniversePage(int,
	 * int)
	 */
	public List<Universe> getUniversePage(int pageNumber, int entriesPerPage)
			throws GameException {
		try {
			return universe_table.getUniversePage(pageNumber, entriesPerPage);
		} catch (SQLException e) { // everything is closed already
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see DataConnectionInterface.GameDataConnectionInterface#getUserPage(int,
	 * int)
	 */
	public List<User> getUserPage(int pageNumber, int entriesPerPage)
			throws GameException {
		try {
			return user_table.getUserPage(pageNumber, entriesPerPage);
		} catch (SQLException e) { // everything is already closed
			throw new GameException(1, e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see DataConnectionInterface.GameDataConnectionInterface#getCardPage(int,
	 * int)
	 */
	public List<ServerCard> getCardPage(int universe_id, int pageNumber,
			int entriesPerPage) throws GameException {
		try {
			return character_in_universe_table.getCharacterPage(universe_id,
					pageNumber, entriesPerPage);
		} catch (SQLException e) { // everything is already closed
			throw new GameException(1, e.getMessage());
		}
	}

	/**
	 * Imports all data into the database
	 * 
	 * @param character_tsv
	 *            - path of fictional_character tsv file
	 * @param universe_tsv
	 *            - path of fictional_universe tsv file
	 * @throws GameException
	 */
	private void BuildDataBase(final String character_tsv,
			final String universe_tsv) throws GameException {
		
		try {
			// dropping existing tables. Order matters, due to foreign keys
			user_table.DropTable();
			character_in_universe_table.DropTable();
			character_table.DropTable();
			universe_table.DropTable();
			
			character_table.CreateTable();
			universe_table.CreateTable();
			character_in_universe_table.CreateTable();
			user_table.CreateTable();
			
			// import data
			FileDumper dumper = new FileDumper();
			dumper.DumpFileToTable(character_tsv, character_table, new int[] {
					0, 11 }, true);
			dumper.DumpFileToTable(universe_tsv, universe_table, new int[] { 0 }, false);

			// insert 'Other' universe
			addUniverse("Other");

			// remove with same name
			// insert unique constraint
			String Xid = "X." + universe_table.GetPrimaryKey();
			String Yid = "Y." + universe_table.GetPrimaryKey();
			// get universes that appears more then once with the same name
			String getRepeatingUniverses = "SELECT DISTINCT X.name, " + Xid;
			getRepeatingUniverses += " FROM " + universe_table.GetTableName()
					+ " AS X, ";
			getRepeatingUniverses += universe_table.GetTableName() + " AS Y";
			getRepeatingUniverses += " WHERE " + Xid + " <> " + Yid + " AND "
					+ "X.name = Y.name";

			List<String> universeNames = new ArrayList<String>();
			List<Integer> universesToDelete = new ArrayList<Integer>();
			ResultSet repeatingUniverses = db_mgr
					.QueryTable(getRepeatingUniverses);
			while (repeatingUniverses.next()) {
				String universeName = repeatingUniverses.getString(1);
				int universeID = repeatingUniverses.getInt(2);
				if (universeNames.contains(universeName))
					universesToDelete.add(universeID);
				else
					universeNames.add(universeName);
			}

			for (int universe_id : universesToDelete)
				deleteUniverse(universe_id);

			db_mgr.closeLastStatement();
			// add unique constraint
			universe_table.addUniqueConstraint("name");

			// close everything, again giving message of first error that occurred
		} catch (SQLException e) {
			try {
				db_mgr.closeLastStatement();
			} catch (SQLException e1) {
				throw new GameException(1, e.getMessage());
			}
			throw new GameException(1, e.getMessage());
		} catch (FileNotFoundException e) {
			try {
				db_mgr.closeLastStatement();
			} catch (SQLException e1) {
				throw new GameException(2, e.getMessage());
			}
			throw new GameException(2, e.getMessage());
		} catch (IOException e) {
			try {
				db_mgr.closeLastStatement();
			} catch (SQLException e1) {
				throw new GameException(3, e.getMessage());
			}
			throw new GameException(3, e.getMessage());
		}
	}

	/**
	 * Builds the table that connects universes and their characters
	 * 
	 * @throws GameException
	 */
	private void CreateCharacterInUniverseTable() throws GameException {

		String firstQuery;
		// returns id's for all characters whose
		// appears_in_these_fictinal_universes is not null
		firstQuery = "SELECT DISTINCT " + character_table.GetPrimaryKey()
				+ ", appears_in_these_fictional_universes";
		firstQuery += " FROM " + character_table.GetTableName();
		firstQuery += " WHERE appears_in_these_fictional_universes IS NOT NULL";
		ResultSet charactersWithUniverses = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.Statement stmt = null;
		ResultSet actualUniversesOfTheCharacter = null;
		try {
			// prepare a statement for insertion
			charactersWithUniverses = db_mgr.QueryTable(firstQuery);
			// save the statement which will be overriden by future statements,
			// so that we will close it later
			stmt = db_mgr.getLastStatement();
			String insertInto = "INSERT INTO "
					+ character_in_universe_table.GetTableName() + "("
					+ universe_table.GetPrimaryKey() + ", "
					+ character_table.GetPrimaryKey() + ")";
			String insertInstruction = insertInto + " VALUES(?,?)";
			pstmt = db_mgr.getPreparedStatement(insertInstruction);
			while (charactersWithUniverses.next()) {
				// appears_in_these_fictional_universes appears in the form:
				// universe1,universe2,universe3
				// so we divide it into universe names, and then check
				// that these names actually appear in the fictional_universe
				// table
				// in that case, we add it to the table
				String[] universesOfTheCharacter = charactersWithUniverses
						.getString(2).split(",");

				// remove duplicates
				universesOfTheCharacter = Misc
						.removeDuplicates(universesOfTheCharacter);
				for (String universeName : universesOfTheCharacter) {
					String secondQuery;
					// get universes with the required name
					secondQuery = "SELECT DISTINCT "
							+ universe_table.GetPrimaryKey();
					secondQuery += " FROM " + universe_table.GetTableName();
					secondQuery += " WHERE name = '"
							+ Misc.correctString(universeName) + "'";
					actualUniversesOfTheCharacter = db_mgr
							.QueryTable(secondQuery);

					// insert them to the new table
					while (actualUniversesOfTheCharacter.next()) {
						pstmt
								.setInt(1, actualUniversesOfTheCharacter
										.getInt(1));
						pstmt.setInt(2, charactersWithUniverses.getInt(1));
						pstmt.addBatch();
					}
					db_mgr.closeLastStatement();
				}
			}
			pstmt.executeBatch();

			db_mgr.setLastStatement(stmt);
			stmt = null;
			db_mgr.closeLastStatement();
			pstmt.close();
			pstmt = null;

			// now add characters with no universe to the "Other" universe
			String thirdQuery = "SELECT DISTINCT "
					+ universe_table.GetPrimaryKey() + ", "
					+ character_table.GetPrimaryKey();
			thirdQuery += " FROM " + universe_table.GetTableName() + ", "
					+ character_table.GetTableName();
			thirdQuery += " WHERE " + universe_table.GetTableName()
					+ ".name = 'Other' AND " + character_table.GetPrimaryKey()
					+ " NOT IN ";

			// insert them using a query
			String innerQuery = "(SELECT character_id FROM "
					+ character_in_universe_table.GetTableName() + ")";
			thirdQuery += innerQuery;
			character_in_universe_table.InsertTableUsingQuery(thirdQuery,
					new String[] { "universe_id", "character_id" });

			// closing mechanism (always tries to close everything)
			// if there are several errors, we give the message of the first one
		} catch (SQLException e) {
			closeProperly(stmt, pstmt);
			throw new GameException(1, e.getMessage());
		}
	}

	/**
	 * Ugly code to ensure everything is being closed
	 * @param stmt - statement we may need to close
	 * @param pstmt - prepared statement we may need to close
	 */
	private void closeProperly(Statement stmt, PreparedStatement pstmt) {
		try {
			db_mgr.closeLastStatement();
		} catch (Exception e) {
		} finally {
			try {
				if (stmt != null) {
					db_mgr.setLastStatement(stmt);
					db_mgr.closeLastStatement();
				}
			} catch (Exception e) {
			} finally {
				try {
				if (pstmt != null)
					pstmt.close();
				} catch (Exception e) {}
			}
		}
	}

	// database manager and the different tables
	private DBManager db_mgr;
	private UserTable user_table;
	private CharacterTable character_table;
	private UniverseTable universe_table;
	private CharacterInUniverseTable character_in_universe_table;

}
